<?php

function list_gap_kompetensi(){
	
	$header = array(
		'No Posisi '=>array('style'=>'border-bottom:2px solid;width:15%'),  
		'Jabatan'=>array('style'=>'border-bottom:2px solid;width:40%'),  
		'Gap Specification'=>array('style'=>'border-bottom:2px solid;width:10%'),  
		'Gap Kompetensi'=>array('style'=>'border-bottom:2px solid;width:10%'),  
		'Gap Physical'=>array('style'=>'border-bottom:2px solid;width:10%'),  
		' '=>array('style'=>'border-bottom:2px solid;width:15%'), 
	);
	$query = "SELECT a.* FROM jabatan a	
		INNER JOIN sk_jabatan b ON b.sk_id=a.sk_id
		WHERE b.active='Y' 
		ORDER BY a.urut" ;
	$result = my_query($query);
	$row = array();
	while($ey = my_fetch_array($result) ){
		$query = "SELECT karyawan_id FROM kekaryawanan 
			WHERE jabatan_id =".$ey[jabatan_id]."  
			AND karyawan_id NOT IN (select karyawan_id FROM pemberhentian)";
		$karyawans = my_query($query);
		$kompetensi_gap = $physical_gap  = $spec_gap= 0;
		while($row1 = my_fetch_array($karyawans) ){
			if(is_gap_spec($ey[jabatan_id],$row1[karyawan_id])){
				$spec_gap++; 
			}
			
			if(is_gap_kompetensi($ey[jabatan_id],$row1[karyawan_id])){
				$kompetensi_gap++; 
			}
			
			if(is_gap_physical($ey[jabatan_id],$row1[karyawan_id])){
				$physical_gap++; 
			}
		}
		
		if($spec_gap>0){
			$specproperty = array(
				'href'=>'index.php?com=gap_kompetensi&task=spec&id='.$ey['jabatan_id'], 
				'title'=>'Gap Spesification'
			);
			$spec_button = button_icon( 'b_props.png' , $specproperty  );
		}else{
			$spec_button = button_icon( 'bd_deltbl.png' , ''  );		
		}
		
		if($kompetensi_gap>0){		
			$kompetensiproperty = array(
						'href'=>'index.php?com=gap_kompetensi&task=kompetensi&id='.$ey['jabatan_id'], 
						'title'=>'Gap Kompetensi'
				);
			$kompetensi_button = button_icon( 'b_props.png' , $kompetensiproperty  );
		}else{
			$kompetensi_button = button_icon( 'bd_deltbl.png' , ''  );		
		}
	
		if($physical_gap>0){		
			$physicalproperty = array(
						'href'=>'index.php?com=gap_kompetensi&task=physic&id='.$ey['jabatan_id'], 
						'title'=>'Gap Physic'
				);
			$physical_button = button_icon( 'b_props.png' , $physicalproperty  );
		}else{
			$physical_button = button_icon( 'bd_deltbl.png' , ''  );		
		}	
		if($kompetensi_gap>0 OR $physical_gap>0 OR $spec_gap>0 ){ 			
			$row[] = array(
				'nomor_org'=>$ey['nomor_posisi'], 
				'jabatan'=> $ey['nama_jabatan'] ,
				'gap_spec'=> $spec_gap,
				'gap_kom'=> $kompetensi_gap,
				'gap_physic'=> $physical_gap,
				'operasi'=> position_text_align(  $spec_button.' '. $kompetensi_button.' '.$physical_button , 'right'),  
			);			
		}	
	}	
	$datas = table_rows($row);
	return table_builder($header , $datas , 6 , false );;
/*	
	$menuontop = array(
		'Cetak' => array('onclick'=>'javascript:window.open(\'index.php?com=report&task=cetak_departemen\',\'mywin\',\'left=20,top=20,width=800,height=600,toolbar=0,resizable=0\');')
	);
	$printmenu = additional_menu_on_list( $menuontop ,'');
	return $printmenu.table_builder($header , $datas ,  5 , false );;
*/	
}

function is_gap_spec($jabatan_id,$karyawan_id){
	if(is_gap_golongan_jabatan($jabatan_id,$karyawan_id)){
		return true;
	}
	if(is_gap_pendidikan($jabatan_id,$karyawan_id)){
		return true;
	}
	return false ;
}

function is_gap_kompetensi($jabatan_id,$karyawan_id){

	$query = "SELECT kompetensi_id,level FROM jabatan_kompetensi_link WHERE jabatan_id = {$jabatan_id} ";
	$res = my_query($query);
	while($row = my_fetch_array($res) ){
		$kompetensi_id=$row['kompetensi_id'] ;
		$query = "SELECT skor FROM kekaryawanan_kompetensi WHERE 
			kompetensi_id = {$kompetensi_id} 
			AND karyawan_id = {$karyawan_id} ";
		$result = my_query($query);
		$skors = my_fetch_array($result) ;
		if($skors['skor']<$row['level']){
			return true ;
		}
	}
	return false ;
}

function is_gap_physical($jabatan_id,$karyawan_id){

	$query = "SELECT physical_id,jawaban FROM jabatan_physical_link 
	WHERE jabatan_id = {$jabatan_id} ";
	$res = my_query($query);
	while($row = my_fetch_array($res) ){
		$physical_id=$row['physical_id'] ;
		$query = "SELECT jawaban FROM karyawan_physical_link WHERE 
			physical_id = {$physical_id} 
			AND karyawan_id = {$karyawan_id} ";
		$result = my_query($query);
		$jawabans = my_fetch_array($result) ;
		
		if($jawabans['jawaban']<>$row['jawaban']){
			return true;
		}
	}
	return false  ;
}

function is_gap_golongan_jabatan($jabatan_id,$karyawan_id){

	$query = "SELECT golongan_jabatan_id FROM kekaryawanan WHERE 
		karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$gols = my_fetch_array($res) ;
	$gj_k = $gols['golongan_jabatan_id'] ;

	$query = "SELECT golongan_jabatan_id  FROM jabatan_golongan_link 
	WHERE jabatan_id = {$jabatan_id} ";
	$res = my_query($query);
	
	if(my_num_rows($res)==0){ ;
		return false ;
	}	
	while($row = my_fetch_array($res) ){
		if($gj_k==$row['golongan_jabatan_id']){
			return false ;
		}
	}
	return true ;
}

function is_gap_pendidikan($jabatan_id,$karyawan_id){
/*
	$query = "SELECT masa_kerja, level_pendidikan_id  FROM jabatan_pendidikan_link WHERE 
		karyawan_id = {$karyawan_id} ";
	$res = my_query($query);
	$gols = my_fetch_array($res) ;
	$gj_k = $gols['golongan_jabatan_id'] ;
*/
	$pendidikan_k = 0 ;
	$masa_kerja_k = 0 ;
	$query = "SELECT masa_kerja, level_pendidikan_id  FROM jabatan_pendidikan_link 
	WHERE jabatan_id = {$jabatan_id} ";
	$res = my_query($query);

	if(my_num_rows($res)==0){ ;
		return false ;
	}	
	while($row = my_fetch_array($res) ){
		if($pendidikan_k==$row['level_pendidikan_id'] 
			AND $masa_kerja_k==$row['masa_kerja']){
			return false ;
		}
	}
	return true ;
}

function list_kompetensi($jabatan_id){
	$header  = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:5%'), 
		'Karyawan'=>array('style'=>'border-bottom:2px solid;width:30%'),  
		'Kompetensi'=>array('style'=>'border-bottom:2px solid;width:35%'),   
		'Std'=>array('style'=>'border-bottom:2px solid;width:10%'),   
		'Act'=>array('style'=>'border-bottom:2px solid;width:10%'),   
		'Gap'=>array('style'=>'border-bottom:2px solid;width:10%'),   
	);
	$query = "SELECT karyawan_id FROM kekaryawanan  
		WHERE jabatan_id =".$jabatan_id."  
		AND karyawan_id NOT IN (select karyawan_id FROM pemberhentian)";
	$i = 0;
	$result = my_query($query);
	$row = array();
	while($ey = my_fetch_array($result) ){
		$karyawan_id=$ey['karyawan_id'];
		$karyawan = loaddata_karyawan($karyawan_id);
		$query = "SELECT kompetensi_id,level FROM jabatan_kompetensi_link WHERE jabatan_id = {$jabatan_id} ";
		$result1 = my_query($query);
		$karyawan_sama=false;
		while($ey1 = my_fetch_array($result1) ){
			$kompetensi_id=$ey1['kompetensi_id'] ;
			$kompetensis = my_get_data_by_id('kompetensi','kompetensi_id',$kompetensi_id);
			$query = "SELECT skor FROM kekaryawanan_kompetensi WHERE 
				kompetensi_id = {$kompetensi_id} 
				AND karyawan_id = {$karyawan_id} ";
			$result2 = my_query($query);
			$skors = my_fetch_array($result2) ;
			if($skors['skor']<$ey1['level']){
				if($karyawan_sama){
					$row[] = array(
						'#'=>position_text_align (' ', 'center'),    
						'karyawan'=>' ',
						'Kompetensi'=>$kompetensis['nama_kompetensi'],   
						'Std'=>$ey1['level'],   
						'Act'=>$skors['skor'],   
						'Gap'=>($ey1['level'] - $skors['skor']),   
					);
				}else{
					$i++;
					$row[] = array(
						'#'=>position_text_align ($i, 'center'),    
						'karyawan'=>$karyawan['nama_gelar'],
						'Kompetensi'=>$kompetensis['nama_kompetensi'],   
						'Std'=>$ey1['level'],   
						'Act'=>$skors['skor'],   
						'Gap'=>($ey1['level'] - $skors['skor']),   
					);
					$karyawan_sama=true;
				}
			}	
		};
	};
	$datas = table_rows($row);
	$jabatan = loaddata_jabatan($jabatan_id) ;

	$view .= form_header( "..." , "..."  );
	$view .= '<tr><td><td><div style="width:100%;text-align:right;">
	<input type="button" value="Back" class="main_button" onclick="javascript:location.href=\'index.php?com=gap_kompetensi\'"/>
	</div></td></tr>'   ; 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nomor_posisi'] .'</span>'  , "<b>Nomor Posisi</b>"    );  	
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nama_jabatan'].'</span>'   , "<b>Jabatan</b>"    );  
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_departemen'] .'</span>'  , "<b>Departemen</b>"    ); 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_direktorat'] .'</span>'  , "<b>Direktorat</b>"    ); 
	return  $view. table_builder($header , $datas ,  3 , false );
}

function list_physic($jabatan_id){
	$header  = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:5%'), 
		'Karyawan'=>array('style'=>'border-bottom:2px solid;width:25%'),  
		'Physical Ability'=>array('style'=>'border-bottom:2px solid;width:30%'),   
		'Std'=>array('style'=>'border-bottom:2px solid;width:20%'),   
		'Act'=>array('style'=>'border-bottom:2px solid;width:20%'),   
	);
	$query = "SELECT karyawan_id FROM kekaryawanan 
		WHERE jabatan_id =".$jabatan_id."  
		AND karyawan_id NOT IN (select karyawan_id FROM pemberhentian)";
	$i = 0;
	$result = my_query($query);
	$row = array();
	while($ey = my_fetch_array($result) ){
		$karyawan_id=$ey['karyawan_id'];
		$karyawan = loaddata_karyawan($karyawan_id);
		$query = "SELECT physical_id,jawaban FROM jabatan_physical_link 
			WHERE jabatan_id = {$jabatan_id} ";
		$result1 = my_query($query);
		$karyawan_sama=false;
		while($ey1 = my_fetch_array($result1) ){
			$physical_id=$ey1['physical_id'] ;
			$physicals = my_get_data_by_id('jabatan_physical','physical_id',$physical_id);
			$query = "SELECT jawaban FROM karyawan_physical_link WHERE 
				physical_id = {$physical_id} 
				AND karyawan_id = {$karyawan_id} ";
			$result2 = my_query($query);
			$jawabans = my_fetch_array($result2) ;
			if($jawabans['jawaban']<>$ey1['jawaban']){			
				if($karyawan_sama){
					$row[] = array(
						'#'=>position_text_align (' ', 'center'),    
						'karyawan'=>' ',
						'physical'=>$physicals['nama_physical'],   
						'Std'=>$ey1['jawaban'],   
						'Act'=>$jawabans['jawaban'],   
					);
				}else{
					$i++;
					$row[] = array(
						'#'=>position_text_align ($i, 'center'),    
						'karyawan'=>$karyawan['nama_gelar'],
						'Kompetensi'=>$physicals['nama_physical'],   
						'Std'=>$ey1['jawaban'],   
						'Act'=>$jawabans['jawaban'],   
					);
					$karyawan_sama=true;
				}
			}	
		};
	};
	$datas = table_rows($row);
	$jabatan = loaddata_jabatan($jabatan_id) ;
	$view .= form_header( "..." , "..."  );
	$view .= '<tr><td><td><div style="width:100%;text-align:right;">
	<input type="button" value="Back" class="main_button" onclick="javascript:location.href=\'index.php?com=gap_kompetensi\'"/>
	</div></td></tr>'   ; 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nomor_posisi'] .'</span>'  , "<b>Nomor Posisi</b>"    );  	
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nama_jabatan'].'</span>'   , "<b>Jabatan</b>"    );  
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_departemen'] .'</span>'  , "<b>Departemen</b>"    ); 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_direktorat'] .'</span>'  , "<b>Direktorat</b>"    ); 
	return  $view. table_builder($header , $datas ,  3 , false );
}

function list_spec($jabatan_id){
	$header  = array(
		'#'=>array('style'=>'border-bottom:2px solid;width:5%'), 
		'Karyawan'=>array('style'=>'border-bottom:2px solid;width:25%'),  
		'Specification'=>array('style'=>'border-bottom:2px solid;width:30%'),   
		'Std'=>array('style'=>'border-bottom:2px solid;width:20%'),   
		'Act'=>array('style'=>'border-bottom:2px solid;width:20%'),   
	);
	$query = "SELECT karyawan_id FROM kekaryawanan 
		WHERE jabatan_id =".$jabatan_id."  
		AND karyawan_id NOT IN (select karyawan_id FROM pemberhentian)";
	$i = 0;
	$result = my_query($query);
	$row = array();
	while($ey = my_fetch_array($result) ){
		$karyawan_id=$ey['karyawan_id'];
		$karyawan = loaddata_karyawan($karyawan_id);
		// GOLONGAN_JABATAN
		$query = "SELECT a.golongan_jabatan_id,b.kode_golongan_jabatan FROM kekaryawanan a 
			inner join golongan_jabatan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
			WHERE a.karyawan_id = {$karyawan_id} ";
		$res = my_query($query);
		$gols = my_fetch_array($res) ;
		$gj_k=$gols['kode_golongan_jabatan'] ;
		$query = "SELECT a.golongan_jabatan_id,b.kode_golongan_jabatan  FROM jabatan_golongan_link a 
			inner join golongan_jabatan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
			WHERE a.jabatan_id = {$jabatan_id} ";
		
		$res = my_query($query);
		$gj_j='';
		$karyawan_sama=false;
		while($ey1 = my_fetch_array($res)){			
			if($gj_k==$ey1['kode_golongan_jabatan']){
				$gj_j='' ;
				break;
			}		
			$gj_j.=','.$ey1['kode_golongan_jabatan'];
		}
		$gj_j=substr($gj_j,1);
		if($gj_j==''){
		}else{
			if($karyawan_sama){
				$row[] = array(
					'#'=>position_text_align (' ', 'center'),    
					'karyawan'=>' ',
					'spec'=>'Golongan Jabatan',   
					'Std'=>$gj_j,   
					'Act'=>$gj_k,   
				);
			}else{
				$i++;
				$row[] = array(
					'#'=>position_text_align ($i, 'center'),    
					'karyawan'=>$karyawan['nama_gelar'],
					'spec'=>'Golongan Jabatan',   
					'Std'=>$gj_j,   
					'Act'=>$gj_k,   
				);
				$karyawan_sama=true;
			}
		}	
		// PENDIDIKAN & MASA KERJA
		/*
		$query = "SELECT a.golongan_jabatan_id,b.kode_golongan_jabatan FROM kekaryawanan a 
			inner join golongan_jabatan b ON a.golongan_jabatan_id=b.golongan_jabatan_id 
			WHERE a.karyawan_id = {$karyawan_id} ";
		$res = my_query($query);
		$gols = my_fetch_array($res) ;
		$gj_k=$gols['kode_golongan_jabatan'] ;
		*/
		$spec_k='' ;
		$query = "SELECT a.masa_kerja, a.level_pendidikan_id, b.label  FROM jabatan_pendidikan_link a 
			inner join pendidikan b ON a.level_pendidikan_id=b.pendidikan_id 
			WHERE jabatan_id = {$jabatan_id} ";		
		
		$res = my_query($query);
		$spec_j='';
		while($ey1 = my_fetch_array($res) ){
			if($spec_k==$ey1['label'].'/'.$ey1['masa_kerja']){
				$spec_j='' ;
				break;
			}	
			$spec_j.=','.$ey1['label'].'/'.$ey1['masa_kerja'];
		}
		$spec_j=substr($spec_j,1);
		if($spec_j==''){
		}else{		
			if($karyawan_sama){
				$row[] = array(
					'#'=>position_text_align (' ', 'center'),    
					'karyawan'=>' ',
					'spec'=>'Pendidikan/Masa Kerja',   
					'Std'=>$spec_j,   
					'Act'=>$spec_k,   
				);
			}else{
				$i++;
				$row[] = array(
					'#'=>position_text_align ($i, 'center'),    
					'karyawan'=>$karyawan['nama_gelar'],
					'spec'=>'Pendidikan/Masa Kerja',   
					'Std'=>$spec_j,   
					'Act'=>$spec_k,   
				);
				$karyawan_sama=true;
			}
		}		
	};
	$datas = table_rows($row);
	$jabatan = loaddata_jabatan($jabatan_id) ;
	$view .= form_header( "..." , "..."  );
	$view .= '<tr><td><td><div style="width:100%;text-align:right;">
	<input type="button" value="Back" class="main_button" onclick="javascript:location.href=\'index.php?com=gap_kompetensi\'"/>
	</div></td></tr>'   ; 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nomor_posisi'] .'</span>'  , "<b>Nomor Posisi</b>"    );  	
	$view .= form_field_display( '<span class="label_form">'.$jabatan['nama_jabatan'].'</span>'   , "<b>Jabatan</b>"    );  
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_departemen'] .'</span>'  , "<b>Departemen</b>"    ); 
	$view .= form_field_display( '<span class="label_form">'.$jabatan['organisasi_direktorat'] .'</span>'  , "<b>Direktorat</b>"    ); 
	return  $view. table_builder($header , $datas ,  3 , false );
}